CONTENTS

Chapter 4. Database Connectivity: JDBC

In the summer of 1996, Sun released the first version of the Java Database Connectivity (JDBC) kit. This package lets programmers connect to a database, query it, or update it, using the Structured Query Language or SQL. (SQL, usually pronounced like "sequel," is an industry standard for database access.) When JDBC was first announced, we considered this one of the most important developments in programming for the Java platform. It is not just that databases are among the most common use of hardware and software today. After all, there are a lot of products running after this market, so why did we think the Java programming language had the potential to make a big splash? The reason that Java and JDBC have an essential advantage over other database programming environments is this:

The same database program written in the Java programming language can run on an NT box, a Solaris server, or a database appliance powered by the Java platform. You can move your data from one database to another, for example, from Microsoft SQL Server to Oracle, or even to a tiny database embedded in a device, and the same program can still read your data. This is in sharp contrast to traditional database programming. It is all too common that one writes database applications in a proprietary database language, using a database management system that is available only from a single vendor. The result is that you can run the resulting application only on one or two platforms. We believe that because of their universality, the Java programming language and JDBC will eventually replace proprietary database languages and call level interfaces used by vendors such as Oracle, Informix, and Microsoft for accessing databases.

NOTE

graphics/note.gif
 

Some database vendors now build a Java Virtual Machine into the database itself so that you can write stored procedures in Java. This technology is part of the SQLJ specification. For more information on SQLJ, see http://www.sqlj.org.

As part of the release of Java 2 in 1998, a second version of JDBC was issued as well. At the time of this writing, support for JDBC 2 is still not universal, but many JDBC 2 drivers are available. JDBC 2 introduces several major features such as scrollable cursors and support for advanced SQL types. The JDBC 3 specification was in its final review phase as this chapter was finished. JDBC 3 enhancements are more technical in nature. In this chapter, we alert you when you need to be aware of the version differences.

We still must caution you that the JDK offers no tools for database programming with the Java programming language. For form designers, query builders, and report generators, you need to turn to third-party packages. "Corporate" or "professional" versions of development environments for the Java platform, such as Visual Café and JBuilder, ship with database integration tools.

In this chapter:

NOTE

graphics/note.gif
 

Over the years, many technologies were invented to make database access more efficient and fail-safe. Standard databases support indexes, triggers, stored procedures, and transaction management. JDBC supports all these features, but we do not discuss them in detail in this chapter. One could write an entire book on advanced database programming for the Java platform, and many such books have been written. The material in this chapter will give you enough information to effectively use JDBC with a departmental database. To go further with JDBC, we suggest JDBC API Tutorial and Reference by Seth White, Maydene Fisher, Rick Cattell, Graham Hamilton, and Mark Hapner [Addison-Wesley 1999].

The Design of JDBC

From the start, the developers of the Java technology at Sun were aware of the potential that Java showed for working with databases. Starting in 1995, they began working on extending the standard Java library to deal with SQL access to databases. What they first hoped to do was to extend Java so that it could talk to any random database, using only "pure" Java. It didn't take them very long to realize that this is an impossible task: there are simply too many databases out there, using too many protocols. Moreover, while database vendors were all in favor of Sun providing a standard network protocol for database access, they were only in favor of it if Sun decided to use their network protocol.

What all the database vendors and tool vendors did agree on was that it would be useful if Sun provided a pure Java API for SQL access along with a driver manager to allow third-party drivers to connect to specific databases. Database vendors could provide their own drivers to plug into the driver manager. There would then be a simple mechanism for registering third-party drivers with the driver manager—the point being that all the drivers needed to do was follow the requirements laid out in the driver manager API.

After a fairly long period of public discussion, the API for database access became the JDBC API, and the rules for writing drivers were encapsulated in the JDBC service provider interface, or SPI. (The SPI is of interest only to database vendors and database tool providers; we don't cover it here.)

This protocol follows the very successful model of Microsoft's ODBC, which provided a C programming language interface for database access. Both JDBC and ODBC are based on the same idea: Programs written according to the JDBC API would talk to the JDBC driver manager, which, in turn, would use the drivers that were plugged into it at that moment to talk to the actual database.

NOTE

graphics/note.gif
 

A list of JDBC drivers currently available can be found at the web site http://industry.java.sun.com/products/jdbc/drivers

More precisely, the JDBC consists of two layers. The top layer is the JDBC API. This API communicates with the JDBC manager driver API, sending it the various SQL statements. The manager should (transparently to the programmer) communicate with the various third-party drivers that actually connect to the database and return the information from the query or perform the action specified by the query.

NOTE

graphics/note.gif
 

The JDBC specification will actually allow you to pass any string to the underlying driver. The driver can pass this string to the database. This feature allows you to use specialized versions of SQL that may be supported by the driver and its associated database.

All this means the Java/JDBC layer is all that most programmers will ever have to deal with. Figure 4-1 illustrates what happens.

Figure 4-1. JDBC-to-database communication path

graphics/04fig01.gif

JDBC drivers are classified into the following types:

Most database vendors supply either a type 3 or type 4 driver with their database. Furthermore, a number of third-party companies specialize in producing drivers with better standards conformance, support for more platforms, better performance, or, in some cases, simply better reliability than the drivers that are provided by the database vendors.

In summary, the ultimate goal of the JDBC is to make possible the following:

NOTE

graphics/note.gif
 

If you are curious as to why Sun just didn't adopt the ODBC model, their response, as given at the JavaOne conference in May 1996, was:

  • ODBC is hard to learn.

  • ODBC has a few commands with lots of complex options. The preferred style in the Java programming language is to have simple and intuitive methods, but to have lots of them.

  • ODBC relies on the use of void* pointers and other C features that are not natural in the Java programming language.

  • An ODBC-based solution is inherently less safe and harder to deploy than a pure Java solution.

Typical Uses of JDBC

You can use JDBC in both applications and applets. In an applet, all the normal security restrictions apply. By default, the security manager assumes that all applets written in the Java programming language are untrusted.

In particular, applets that use JDBC are only able to open a database connection to the server from which they are downloaded. That means the Web server and the database server must be the same machine, which is not a typical setup. Of course, the Web server can have a proxy service that routes database traffic to another machine. With signed applets, this restriction can be loosened.

Applications, on the other hand, have complete freedom to access remote database servers. If you implement a traditional client/server program (see Figure 4-2), it probably makes more sense to use an application, not an applet, for database access.

Figure 4-2. A client/server application

graphics/04fig02.gif

However, the world is moving away from client/server and toward a "three-tier model" or even more advanced "n-tier models." In the three-tier model, the client does not make database calls. Instead, it calls on a middleware layer on the server that in turn makes the database queries. The three-tier model has a couple of advantages. It separates visual presentation (on the client) from the business logic (in the middle tier) and the raw data (in the database). Therefore, it becomes possible to access the same data and the same business rules from multiple clients, such as a Java application or applet or a web form.

Communication between the client and middle tier can occur through HTTP (when you use a web browser as the client), RMI (when you use an application or applet—see Chapter 5), or another mechanism. JDBC is used to manage the communication between the middle tier and the back-end database. Figure 4-3 shows the basic architecture. There are, of course, many variations of this model. In particular, the Java 2 Enterprise Edition defines a structure for application servers that manage code modules called Enterprise JavaBeans, and provide valuable services such as load balancing, request caching, security, and simple database access. In that architecture, JDBC still plays an important role for issuing complex database queries. (For more information on the Enterprise Edition, see http://java.sun.com/j2ee.)

Figure 4-3. A three-tier application

graphics/04fig03.gif

The Structured Query Language

JDBC is an interface to SQL, which is the interface to essentially all modern relational databases. Desktop databases usually have a graphical user interfaces that lets users manipulate the data directly, but server-based databases are accessed purely through SQL. Most desktop databases have a SQL interface as well, but it often does not support the full range of ANSI SQL92 features, the current standard for SQL.

The JDBC package can be thought of as nothing more than an application programming interface (API) for communicating SQL statements to databases. We will give a short introduction to SQL in this section. If you have never seen SQL before, you may not find this material sufficient. If so, you should turn to one of the many books on the topic. We recommend Client/Server Databases by James Martin and Joe Leben [Prentice-Hall 1998] or the venerable and opinionated book A Guide to the SQL Standard by Chris. J. Date [Addison-Wesley 1997].

You can think of a database as a bunch of named tables with rows and columns. Each column has a column name. The rows contain the actual data. These are sometimes called records.

As the example database for this book, we use a set of tables that describe a collection of books on HTML. (Thanks to Cye H. Waldman at http://wwwiz.com/books for supplying the sample data.)

Table 4-1. The Authors table
Author_ID Name URL
ARON Aronson, Larry http://…
ARPA Arpajian, Scott http://…
… … …
Table 4-2. The Books table
Title ISBN Publisher_ID URL Price
Beyond HTML 0-07-882198-3 00788 http://… 27.95
10 Minute Guide to HTML 0-78970541-9 07897 http://… 15.00
… … … … …
Table 4-3. The BooksAuthors table
ISBN Author_ID Seq_No
1-56-884454-9 TAYL 1
1-56884645-2 SMIT 1
1-56884645-2 BEBA 2
… … …
Table 4-4. The Publishers table
Publisher_ID Name URL
01262 Academic Press www.apnet.com/
18835 Coriolis www.coriolis.com/
… … …

Figure 4-4 shows a view of the Books table. Figure 4-5 shows the result of joining this table with a table of publishers. Both the Books and the Publishers table contain a numerical code for the publisher. When we join both tables on the publisher code, we obtain a query result made up of values from the joined tables. Each row in the result contains the information about a book, together with the publisher name and Web page URL. Note that the publisher names and URLs are duplicated across several rows since we have several rows with the same publisher.

Figure 4-4. Sample table containing the HTML books

graphics/04fig04.gif

Figure 4-5. Two tables joined together

graphics/04fig05.gif

The benefit of joining tables is to avoid unnecessary duplication of data in the database tables. For example, a naive database design might have had columns for the publisher name and URL right in the Books table. But then the database itself, and not just the query result, would have many duplicates of these entries. If a publisher's Web address changed, all entries would need to be updated. Clearly, this is somewhat error prone. In the relational model, we distribute data into multiple tables such that no information is ever unnecessarily duplicated. For example, each publisher URL is contained only once in the publisher table. If the information needs to be combined, then the tables are joined.

In the figures, you can see a graphical tool to inspect and link the tables. Many vendors have tools to express queries in a simple form by connecting column names and filling information into forms. Such tools are often called query by example (QBE) tools. In contrast, a query that uses SQL is written out in text, using the SQL syntax. For example:

SELECT Books.ISBN, Books.Price, Books.Title,
   Books.Publisher_Id, Publishers.Name, Publishers.URL
FROM Books, Publishers
WHERE Books.Publisher_Id = Publishers.Publisher_Id

In the remainder of this section, you will learn how to write such queries. If you are already familiar with SQL, just skip this section.

By convention, SQL keywords are written in all caps, although this is not necessary.

The SELECT operation is quite flexible. You can simply select all elements in the Books table with the following query:

SELECT * FROM Books

The FROM statement is required in every SQL SELECT statement. The FROM clause tells the database which tables to examine to find the data.

You can choose the columns that you want.

SELECT ISBN, Price, Title
FROM Books

You can restrict the rows in the answer with the WHERE clause.

SELECT ISBN, Price, Title
FROM Books
WHERE Price <= 29.95

Be careful with the "equals" comparison. SQL uses = and <>, not == or !=, as in the Java programming language, for equality testing.

NOTE

graphics/note.gif
 

Some database vendors support the use of != for inequality testing. This is not standard SQL, so we recommend against using it.

The WHERE clause can also use pattern matching, using the LIKE operator. The wildcard characters are not the usual * and ?, however. Use a % for zero or more characters and an underscore for a single character. For example:

SELECT ISBN, Price, Title
FROM Books
WHERE Title NOT LIKE '%H_M%'

Note that strings are enclosed in single quotes, not double quotes. A single quote inside a string is denoted as a pair of single quotes. For example,

SELECT Title
FROM Books
WHERE Books.Title LIKE '%''%'

reports all titles that contain a single quote.

You can select data from multiple tables.

SELECT * FROM Books, Publishers

Without a WHERE clause, this query is not very interesting. It lists all combinations of rows from both tables. In our case, where Books has 37 rows and Publishers has 18 rows, the result is a table with 37 x 18 entries and lots of duplications. We really want to constrain the query to say that we are only interested in matching books with their publishers.

SELECT * FROM Books, Publishers
WHERE Books.Publisher_Id = Publishers.Publisher_Id

This query result has 37 rows, one for each book, since each book has a publisher in the Publisher table.

Whenever you have multiple tables in a query, the same column name can occur in two different places. That happened in our example. There is a publisher code column called Publisher_Id in both the Books and the Publishers table. When an ambiguity would otherwise result, you must prefix each column name with the name of the table to which it belongs, such as Books.Publisher_Id.

Now you have seen all SQL constructs that were used in the query at the beginning of this section:

SELECT Books.ISBN, Books.Price, Books.Title,
   Books.Publisher_Id,  Publishers.Name, Publishers.URL
FROM Books, Publishers
WHERE Books.Publisher_Id = Publishers.Publisher_Id

You can use SQL to change the data inside a database as well, by using so-called action queries (i.e., queries that move or change data). For example, suppose you want to reduce by $5.00 the current price of all books that do not have "HTML 3" in their title.

UPDATE Books
SET Price = Price - 5.00
WHERE Title NOT LIKE '%HTML 3%'

Similarly, you can change several fields at the same time by separating the SET clauses with commas. There are many other SQL keywords you can use in an action query. Probably the most important besides UPDATE is DELETE, which allows the query to delete those records that satisfy certain criteria. Finally, SQL comes with built-in functions for taking averages, finding maximums and minimums in a column, and a lot more. Consult a book on SQL for more information.

Typically, to insert values into a table, you use the INSERT statement:

INSERT INTO Books
VALUES ('Beyond HTML', '0-07-882198-3', '00788', '', 27.95)

You need a separate INSERT statement for every row being inserted in the table.

Of course, before you can query, modify, and insert data, you must have a place to store data. Use the CREATE TABLE command to make a new table. You specify the name and data type for each column. For example,

CREATE TABLE Books
(
   Title CHAR(60),
   ISBN CHAR(13),
   Publisher_Id CHAR(5),
   URL CHAR(80),
   Price DECIMAL(6,2)
)

Table 4-5 shows the most common SQL data types.

Table 4-5. SQL Data Types
Data Types Description
INTEGER or INT Typically, a 32-bit integer
SMALLINT Typically, a 16-bit integer
NUMERIC(m,n),DECIMAL(m,n) or DEC(m,n) Fixed-point decimal number with m total digits and n digits after the decimal point
FLOAT(n) A floating-point number with n binary digits of precision
REAL Typically, a 32-bit floating-point number
DOUBLE Typically, a 64-bit floating-point number
CHARACTER(n) or CHAR(n) Fixed-length string of length n
VARCHAR(n) Variable-length strings of maximum length n
BOOLEAN A boolean value
DATE Calendar date, implementation dependent
TIME Time of day, implementation dependent
TIMESTAMP Date and time of day, implementation dependent
BLOB A binary large object
CLOB A character large object

In this book, we are not discussing the additional clauses, such as keys and constraints, that you can use with the CREATE TABLE command.

Installing JDBC

First, you need a database program that is compatible with JDBC. You will also need to create a database for your experimental use. We assume you will call this database COREJAVA. Create a new database, or have your database administrator create one with the appropriate permissions. You need to be able to create, update, and drop tables.

If you have never installed a client/server database before, you may find that setting up the database is somewhat complex and that it can be difficult to diagnose the cause for failure. It may be best to seek expert help if your setup is not working correctly.

One alternative is to install a pure Java database such as Cloudscape (http: //www.cloudscape.com), PointBase (http://www.pointbase.com), or InstantDB (http://www.lutris.com/products/instantDB). These databases are less powerful but simple to set up. The most complex part is setting up the class path, which should not pose a challenge for a seasoned Java programmer.

Essentially all database vendors already have JDBC drivers. You need to locate the vendor's instructions to load the driver into your program and to connect to the database. In the next section, we will explain the setups for two typical databases that are available on a variety of platforms:

Instructions for other databases are similar, although, of course, the details will vary.

We recommend against using the JDBC/ODBC bridge driver that comes with the Java 2 SDK. We recommend even more strongly against using that driver with a desktop database such as Microsoft Access. Not only are the installation and configuration somewhat cumbersome, but both the bridge driver and desktop databases have restrictions that can easily lead to confusion. Ultimately, one learns very little about real databases from this setup.

NOTE

graphics/note.gif
 

Some desktop databases support SQL through a proprietary mechanism, not JDBC or ODBC. For example, Borland has a BDE engine and Microsoft has a Jet engine that give somewhat better performance than ODBC for local database access. These mechanisms are not compatible with the Java platform.

Basic JDBC Programming Concepts

Programming with the JDBC classes is, by design, not very different from programming with the usual Java platform classes: you build objects from the JDBC core classes, extending them by inheritance if need be. This section takes you through the details.

NOTE

graphics/note.gif
 

The classes that you use for JDBC programming are contained in the java.sql and javax.sql packages.

Database URLs

When connecting to a database, you must specify the data source and you may need to specify additional parameters. For example, network protocol drivers may need a port, and ODBC drivers may need various attributes.

As you might expect, JDBC uses a syntax similar to ordinary URLs to describe data sources. Here are examples of the syntax:

jdbc:db2:COREJAVA
jdbc:cloudscape:COREJAVA;create=true

These JDBC URLs specify a DB2 or Cloudscape database named COREJAVA. The general syntax is

jdbc:subprotocol name:other stuff

where a subprotocol is used to select the specific driver for connecting to the database.

The format for the other stuff parameter depends on the subprotocol used. You need to look up your vendor's documentation for the specific format.

Making the Connection

You need to find out the names of the JDBC driver classes used by your vendor. Typical driver names are

COM.ibm.db2.jdbc.app.DB2Driver
COM.cloudscape.core.JDBCDriver

Next, you must find the library in which the driver is located, such as db2java.zip or cloudscape.jar. You must place the full path name to that driver onto the class path. Use one of the following three mechanisms:

The DriverManager is the class responsible for selecting database drivers and creating a new database connection. However, before the driver manager can activate a driver, the driver must be registered.

There are two methods for registering drivers. The jdbc.drivers property contains a list of class names for the drivers that the driver manager will register at startup. The names are separated by colons.

You can specify the property with a command-line argument, such as

java -Djdbc.drivers=COM.ibm.db2.jdbc.app.DB2Driver MyProg

Or your application can add read a properties file with the line

jdbc.drivers=COM.ibm.db2.jdbc.app.DB2Driver

and add that setting to the system properties. (See Chapter 2 for more information on properties.)

You can also supply multiple drivers; separate them with colons, such as

COM.ibm.db2.jdbc.app.DB2Driver:COM.cloudscape.core.JDBCDriver

This approach allows users of your application to install appropriate drivers simply by modifying a property file.

Our sample programs read all database parameters from a properties file database.properties. Make sure to edit that file to match your database.

Alternatively, you can manually register a driver by loading its class.

For example,

Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
   // force registration of driver

After registering drivers, you open a database connection with code that is similar to the following example:

String url = "jdbc:db2:COREJAVA";
String username = "db2inst1";
String password = "wombat";
Connection conn = DriverManager.getConnection(url,
    username, password);

The driver manager will try to find a driver that can use the protocol specified in the database URL by iterating through the available drivers currently registered with the driver manager.

For our example programs, we find it convenient to use a properties file to specify the URL, user name, and password in addition to the database driver. A typical properties file has the following contents:

jdbc.drivers=COM.ibm.db2.jdbc.app.DB2Driver
jdbc.url=jdbc:db2:COREJAVA
jdbc.username=db2inst1
jdbc.password=wombat

Here is the code for reading a properties file and opening the database connection.

Properties props = new Properties();
FileInputStream in
   = new FileInputStream("database.properties");
props.load(in);
in.close();

String drivers = props.getProperty("jdbc.drivers");
if (drivers != null)
   System.setProperty("jdbc.drivers", drivers);
String url = props.getProperty("jdbc.url");
String username = props.getProperty("jdbc.username");
String password = props.getProperty("jdbc.password");
return DriverManager.getConnection(url, username, password);

The getConnection method returns a Connection object. In the following sections, you will see how to use the connection object to execute SQL statements.

TIP

graphics/tip.gif
 

A good way to debug JDBC-related problems is to enable JDBC tracing. Call the DriverManager.setLogWriter method to send trace messages to a PrintWriter. The trace output contains a detailed listing of the JDBC activity.

 

Testing Your Database Installation

Setting up JDBC for the first time can be a bit tricky. You need several pieces of vendor-specific information, and the slightest configuration error can lead to very bewildering error messages.

You should first test your database setup without JDBC. Find out how you can connect to your database and enter SQL commands. Unless a database administrator tells you to use another database, create a database called COREJAVA.

Enter the following commands:

CREATE TABLE Greetings (Name CHAR(20))
INSERT INTO Greetings VALUES ('Hello, World!')
SELECT * FROM Greetings

At this point, you should see a display of the "Hello, World!" entry.

Now clean up:

DROP TABLE Greetings

Once you know that your database installation is working, and that you can log onto the database, you need to gather five pieces of information:

  • The database user name and password

  • The name of the database to use (such as COREJAVA)

  • The JDBC URL format

  • The JDBC driver name

  • The location of the library files with the driver code

The first two depend on your database setup. The other three are supplied in the JDBC specific documentation from your database vendor. For example, for IBM DB2, you may have

  • Database user name = db2inst1 (the installation default), password = the password supplied during installation

  • Database name = COREJAVA

  • JDBC URL format = jdbc:db2:databaseName

  • JDBC driver = COM.ibm.db2.jdbc.app.DB2Driver

  • Library file = /usr/IBMdb2/V7.1/java/db2java.zip

For Cloudscape, you may have

  • Database user name and password are empty

  • Database name = COREJAVA

  • JDBC URL format = jdbc:cloudscape:databaseName;create=true

  • JDBC driver = COM.cloudscape.core.JDBCDriver

  • Library file = /usr/local/cloudscape/lib/cloudscape.jar

Example 4-1 is a small test program that you can use to test your JDBC setup. Prepare the database.properties file with the information that you collected. Then start the test program with the driver library on the class path, such as

java -classpath .:/usr/IBMdb2/V7.1/java/db2java.zip TestDB

(Remember to use a semicolon instead of a colon as the path separator on Windows.)

This program executes the same SQL instructions as the manual test. If you get a SQL error message, then you need to keep working on your setup. It is extremely common to make one or more small errors with capitalization, path names, the JDBC URL format, or the database configuration. Once the test program displays "Hello, World!", then everything is fine, and you can move on to the next section.

Example 4-1 TestDB.java
 1. import java.sql.*;
 2. import java.io.*;
 3. import java.util.*;
 4.
 5. /**
 6.    This program tests that the database and the JDBC
 7.    driver are correctly configured.
 8. */
 9. class TestDB
10. {
11.    public static void main (String args[])
12.    {
13.       try
14.       {
15.          Connection conn = getConnection();
16.          Statement stat = conn.createStatement();
17.
18.          stat.execute("CREATE TABLE Greetings (Name CHAR(20))");
19.          stat.execute(
20.             "INSERT INTO Greetings VALUES ('Hello, World!')");
21.
22.          ResultSet result
23.             = stat.executeQuery("SELECT * FROM Greetings");
24.          result.next();
25.          System.out.println(result.getString(1));
26.          result.close();
27.
28.          stat.execute("DROP TABLE Greetings");
29.
30.          stat.close();
31.          conn.close();
32.       }
33.       catch (SQLException ex)
34.       {
35.          while (ex != null)
36.          {
37.             ex.printStackTrace();
38.             ex = ex.getNextException();
39.          }
40.       }
41.       catch (IOException ex)
42.       {
43.          ex.printStackTrace();
44.       }
45.    }
46.
47.    /**
48.       Gets a connection from the properties specified
49.       in the file database.properties
50.       @return the database connection
51.    */
52.    public static Connection getConnection()
53.       throws SQLException, IOException
54.    {
55.       Properties props = new Properties();
56.       FileInputStream in
57.          = new FileInputStream("database.properties");
58.       props.load(in);
59.       in.close();
60.
61.       String drivers = props.getProperty("jdbc.drivers");
62.       if (drivers != null)
63.          System.setProperty("jdbc.drivers", drivers);
64.       String url = props.getProperty("jdbc.url");
65.       String username = props.getProperty("jdbc.username");
66.       String password = props.getProperty("jdbc.password");
67.
68.       return
69.          DriverManager.getConnection(url, username, password);
70.    }
71. }

Executing SQL Commands

To execute a SQL command, you first create a Statement object. The Connection object that you obtained from the call to DriverManager.getConnection can be used to create statement objects.

Statement stat = conn.createStatement();

Next, you place the statement that you want to execute into a string, for example,

String command = "UPDATE Books"
   + " SET Price = Price - 5.00"
   + " WHERE Title NOT LIKE '%HTML 3%'";

Then you call the executeUpdate method of the Statement class:

stat.executeUpdate(command);

The executeUpdate method returns a count of the rows that were affected by the SQL command. For example, the call to executeUpdate in the preceding example returns the number of book records whose price was lowered by $5.00.

The executeUpdate method can execute actions such as INSERT, UPDATE, and DELETE as well as data definition commands such as CREATE TABLE, and DROP TABLE. However, you need to use the executeQuery method to execute SELECT queries. There is also a catch-all execute statement to execute arbitrary SQL statements. It's not as commonly used, except for queries that a user supplies interactively.

When you execute a query, you are interested in the result. The executeQuery object returns an object of type ResultSet that you use to walk through the result a row at a time.

ResultSet rs = stat.executeQuery("SELECT * FROM Books")

NOTE

graphics/note.gif
 

You can use the same Statement object for multiple, unrelated commands. However, a statement has a single open result set. If you issue multiple queries whose results you analyze concurrently, then you need multiple Statement objects.

Be forewarned, though, that at least one commonly used database (Microsoft SQL Server) has a JDBC driver that allows only one active statement at a time. Use the getMaxStatements method of the DatabaseMetaData class to find out the number of concurrently open statements that your JDBC driver supports.

This sounds restrictive, but in practice, you should probably not fuss with multiple concurrent result sets. If the result sets are related, then you should be able to issue a combined query and analyze a single result. It is much more efficient to let the database combine queries than it is for a Java program to iterate through multiple result sets.

The basic loop for analyzing a result set looks like this:

while (rs.next())
{
   look at a row of the result set
}

CAUTION

graphics/caution.gif
 

The iteration protocol of the ResultSet class is slightly different from the protocol of the Iterator and Enumeration interfaces that we discussed in Chapter 2. Here, the iterator is initialized to a position before the first row. You must call the next method once to move it to the first row.

When inspecting an individual row, you will want to know the contents of each column. A large number of accessor methods give you this information.

String isbn = rs.getString(1);
float price = rs.getDouble("Price");

There are accessors for every Java programming language type, such as getString and getDouble. Each accessor has two forms, one that takes a numeric argument and one that takes a string argument. When you supply a numeric argument, you refer to the column with that number. For example, rs.getString(1) returns the value of the first column in the current row.

CAUTION

graphics/caution.gif
 

Unlike array indexes, database column numbers start at 1.

When you supply a string argument, you refer to the column in the result set with that name. For example, rs.getDouble("Price") returns the value of the column with name Price. Using the numeric argument is a bit more efficient, but the string arguments make the code easier to read and maintain.

Each get method will make reasonable type conversions when the type of the method doesn't match the type of the column. For example, the call rs.getString("Price") converts the floating-point value of the Price column to a string.

NOTE

graphics/note.gif
 

SQL data types and Java data types are not exactly the same. See Table 4-6 for a listing of the basic SQL data types and their equivalents in the Java programming language.

 

Table 4-6. SQL data types and their corresponding Java language types
SQL data type Java data type
INTEGER or INT int
SMALLINT short
NUMERIC(m,n), DECIMAL(m,n)or DEC(m,n) java.sql.Numeric
FLOAT(n) double
REAL float
DOUBLE double
CHARACTER(n)or CHAR(n) String
VARCHAR(n) String
BOOLEAN boolean
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
BLOB java.sql.Blob
CLOB java.sql.Clob
ARRAY java.sql.Array

Advanced SQL Types (JDBC 2)

In addition to numbers, strings, and dates, many databases can store large objects such as images or other data. In SQL, binary large objects are called BLOBs, and character large objects are called CLOBs. The getBlob and getClob methods return objects of type BLOB and CLOB. These classes have methods to fetch the bytes or characters in the large objects.

A SQL ARRAY is a sequence of values. For example, in a Student table, you can have a Scores column that is an ARRAY OF INTEGER. The getArray method returns an object of type java.sql.Array (which is different from the java.lang.reflect.Array class that we discussed in Volume 1). The java.sql.Array interface has methods to fetch the array values.

NOTE

graphics/note.gif
 

The BLOB, CLOB, and ARRAY types are features of SQL3. Java platform support for BLOB and CLOB has been greatly enhanced in JDBC 2, and support for ARRAY is a new feature in JDBC 2.

When you get a BLOB or an array from a database, the actual contents are only fetched from the database when you request individual values. This is a useful performance enhancement, since the data can be quite voluminous.

Some databases are able to store user-defined structured types. JDBC 2 supports a mechanism for automatically mapping structured SQL types to Java objects.

However, in this introductory chapter, we do not discuss BLOBs, arrays, and user-defined types any further.

java.sql.DriverManager

graphics/api.gif
 

java.sql.Connection

graphics/api.gif
 

java.sql.Statement

graphics/api.gif
 

java.sql.ResultSet

graphics/api.gif
 

java.sql.SQLException

graphics/api.gif
 

Most JDBC methods throw this exception, and you must be prepared to catch it. The following methods give more information about the exceptions.

Populating a Database

We now want to write our first, real, JDBC program. Of course, it would be nice if we could execute some of the fancy queries that we discussed earlier. Unfortunately, we have a problem: right now, there is no data in the database. And you won't find a database file on the CD-ROM that you can simply copy onto your hard disk for the database program to read, because no database file format lets you interchange SQL relational databases from one vendor to another. SQL does not have anything to do with files. It is a language to issue queries and updates to a database. How the database execsutes these statements most efficiently and what file formats it uses toward that goal are entirely up to the implementation of the database. Database vendors try very hard to come up with clever strategies for query optimization and data storage, and different vendors arrive at different mechanisms. Thus, while SQL statements are portable, the underlying data representation is not.

To get around our problem, we provide you with a small set of data in a series of text files that contain the raw SQL instructions to create the tables and insert the values. We also give you a program that reads a file with SQL instructions, one instruction per line, and executes them.

Specifically, the program reads data from a text file in a format such as

CREATE TABLE Publisher (Publisher_Id char(5), Name char(30), URL char(80))
INSERT INTO Publisher VALUES ('01262', 'Academic Press', 'www.apnet.com')
INSERT INTO Publisher VALUES ('18835', 'Coriolis', 'www.coriolis.com/')
. . .

At the end of this section, you can see the code for the program that reads the SQL statement file and executes the statements. Even if you are not interested in looking at the implementation, you must run this program if you want to execute the more interesting examples in the remainder of this chapter. Run the program as follows:

java ExecSQL Books.sql
java ExecSQL Authors.sql
java ExecSQL Publishers.sql
java ExecSQL BooksAuthors.sql

Before running the program, check the file database.properties. It looks like this:

jdbc.drivers=COM.cloudscape.core.JDBCDriver
jdbc.url=jdbc:cloudscape:COREJAVA;create=true
jdbc.username=
jdbc.password=

These values work for the Cloudscape database; change them if you use another database.

CAUTION

graphics/caution.gif
 

Make sure that both the database driver and the current directory are on the class path. Alternatively, start up the program as

java -classpath driverPath:. ExecSQL SQLfile

The following steps provide an overview of the ExecSQL program.

  1. Connect to the database. The getConnection method reads the properties in the file database.properties and adds the jdbc.drivers property to the system properties. The driver manager uses the jdbc.drivers property to load the appropriate database driver. The getConnection method uses the jdbc.url, jdbc.username, and jdbc.password properties to open the database connection.

  2. Open the file with the SQL commands. If no file was supplied, then prompt the user to enter the commands on the console. In that way, you can use the ExecSQL program to interactively issue simple queries.

  3. Execute each command with the generic execute method. If it returns true, the command had a result set. The four SQL files that we provide for the book database all end in a SELECT * statement so that you can see that the data was successfully inserted.

  4. If there was a result set, print out the result. Because this is a generic result set, we need to use meta data to find out how many columns the result has. You will learn more about meta data later in this chapter.

  5. If there is any SQL exception, we print the exception and any chained exceptions that may be contained in it.

Example 4-2 provides the code for the program.

Example 4-2 ExecSQL.java
  1. import java.io.*;
  2. import java.util.*;
  3. import java.sql.*;
  4.
  5. /**
  6.    Executes all SQL statements in a file.
  7.    Call this program as
  8.    java -classpath driverPath:. ExecSQL commandFile
  9. */
 10. class ExecSQL
 11. {
 12.    public static void main (String args[])
 13.    {
 14.       try
 15.       {
 16.
 17.          Reader reader;
 18.          if (args.length == 0)
 19.             reader = new InputStreamReader(System.in);
 20.          else
 21.             reader = new FileReader(args[0]);
 22.
 23.          Connection conn = getConnection();
 24.          Statement stat = conn.createStatement();
 25.
 26.          BufferedReader in = new BufferedReader(reader);
 27.
 28.          boolean done = false;
 29.          while (!done)
 30.          {
 31.             if (args.length == 0)
 32.                System.out.println(
 33.                   "Enter command or a blank line to exit:");
 34.
 35.             String line = in.readLine();
 36.             if (line == null || line.length() == 0)
 37.                done = true;
 38.             else
 39.             {
 40.                try
 41.                {
 42.                   boolean hasResultSet = stat.execute(line);
 43.                   if (hasResultSet)
 44.                      showResultSet(stat);
 45.                }
 46.                catch (SQLException ex)
 47.                {
 48.                   while (ex != null)
 49.                   {
 50.                      ex.printStackTrace();
 51.                      ex = ex.getNextException();
 52.                   }
 53.                }
 54.             }
 55.          }
 56.
 57.          in.close();
 58.          stat.close();
 59.          conn.close();
 60.       }
 61.       catch (Exception ex)
 62.       {
 63.          ex.printStackTrace();
 64.       }
 65.    }
 66.
 67.    /**
 68.       Gets a connection from the properties specified
 69.       in the file database.properties
 70.       @return the database connection
 71.    */
 72.    public static Connection getConnection()
 73.       throws SQLException, IOException
 74.    {
 75.       Properties props = new Properties();
 76.       FileInputStream in
 77.          = new FileInputStream("database.properties");
 78.       props.load(in);
 79.       in.close();
 80.
 81.       String drivers = props.getProperty("jdbc.drivers");
 82.       if (drivers != null)
 83.          System.setProperty("jdbc.drivers", drivers);
 84.       String url = props.getProperty("jdbc.url");
 85.       String username = props.getProperty("jdbc.username");
 86.       String password = props.getProperty("jdbc.password");
 87.
 88.       return
 89.          DriverManager.getConnection(url, username, password);
 90.    }
 91.
 92.    /**
 93.       Prints a result set.
 94.       @param stat the statement whose result set should be
 95.       printed
 96.    */
 97.    public static void showResultSet(Statement stat)
 98.       throws SQLException
 99.    {
100.       ResultSet result = stat.getResultSet();
101.       ResultSetMetaData metaData = result.getMetaData();
102.       int columnCount = metaData.getColumnCount();
103.
104.       for (int i = 1; i <= columnCount; i++)
105.       {
106.          if (i > 1) System.out.print(", ");
107.          System.out.print(metaData.getColumnLabel(i));
108.       }
109.       System.out.println();
110.
111.       while (result.next())
112.       {
113.          for (int i = 1; i <= columnCount; i++)
114.          {
115.             if (i > 1) System.out.print(", ");
116.             System.out.print(result.getString(i));
117.          }
118.          System.out.println();
119.       }
120.       result.close();
121.    }
122. }

Executing Queries

In this section, we will write a program that executes queries against the COREJAVA database. For this program to work, you must have populated the COREJAVA database with tables, as described in the preceding section.

Figure 4-6 shows the query application.

Figure 4-6. The QueryDB application

graphics/04fig06.gif

You can select the author and the publisher or leave either of them as "Any." Click on "Query"; all books matching your selection will be displayed in the text box.

You can also change the data in the database. Select a publisher and type an amount into the text box next to the "Change prices" button. When you click on the button, all prices of that publisher are adjusted by the amount you entered, and the text area contains a message indicating how many records were changed. However, to minimize unintended changes to the database, you can't change all prices at once. The author field is ignored when you change prices. After a price change, you may want to run a query to verify the new prices.

In this program, we use one new feature, prepared statements. Consider the query for all books by a particular publisher, independent of the author. The SQL query is

SELECT Books.Price, Books.Title
FROM Books, Publishers
WHERE Books.Publisher_Id = Publishers.Publisher_Id
AND Publishers.Name = the name from the list box

Rather than build a separate query command every time the user launches such a query, we can prepare a query with a host variable and use it many times, each time filling in a different string for the variable. That technique gives us a performance benefit. Whenever the database executes a query, it first computes a strategy of how to efficiently execute the query. By preparing the query and reusing it, you ensure that the planning step is done only once. (The reason you do not always want to prepare a query is that the optimal strategy may change as your data changes. You have to balance the expense of optimization versus the expense of querying your data less efficiently.)

Each host variable in a prepared query is indicated with a ?. If there is more than one variable, then you must keep track of the positions of the ? when setting the values. For example, our prepared query becomes

String publisherQuery =
   "SELECT Books.Price, Books.Title " +
   "FROM Books, Publishers " +
   "WHERE Books.Publisher_Id = Publishers.Publisher_Id " +
   "AND Publishers.Name = ?";
PreparedStatement publisherQueryStat
   = conn.prepareStatement(publisherQuery);

Before executing the prepared statement, you must bind the host variables to actual values with a set method. As with the ResultSet get methods, there are different set methods for the various types. Here, we want to set a string to a publisher name.

publisherQueryStat.setString(1, publisher);

The first argument is the host variable that we want to set. The position 1 denotes the first ?. The second argument is the value that we want to assign to the host variable.

If you reuse a prepared query that you have already executed and the query has more than one host variable, all host variables stay bound as you set them unless you change them with a set method. That means you only need to call set on those host variables that change from one query to the next.

Once all variables have been bound to values, you can execute the query

ResultSet rs = publisherQueryStat.executeQuery();

You process the result set in the usual way. Here, we add the information to the text area result.

result.setText("");
while (rs.next())
   result.appendText(rs.getString(1) + ", " +
      rs.getString(2y) + "\n");
rs.close();

There are a total of four prepared queries in this program, one each for the cases shown in Table 4-7.

Table 4-7. Selected queries
Author Publisher
any any
any specified
specified any
specified specified

The price update feature is implemented as a simple UPDATE statement. For variety, we did not choose to make a prepared statement in this case. Note that we call executeUpdate, not executeQuery, since the UPDATE statement does not return a result set and we don't need one. The return value of executeUpdate is the count of changed rows. We display the count in the text area.

String updateStatement = "UPDATE Books ...";
int r = stat.executeUpdate(updateStatement);
result.setText(r + " records updated");

The following steps provide an overview of the program.

  1. Arrange the components in the frame, using a grid bag layout (see Chapter 9 in Volume 1).

  2. Populate the author and publisher text boxes by running two queries that return all author and publisher names in the database.

  3. When the user selects "Query," find which of the four query types needs to be executed. If this is the first time this query type is executed, then the prepared statement variable is null, and the prepared statement is constructed. Then, the values are bound to the query and the query is executed.

    The queries involving authors are more complex. Because a book can have multiple authors, the BooksAuthors table gives the correspondence between authors and books. For example, the book with ISBN number 1-56-604288-7 has two authors with codes HARR and KIDD. The BooksAuthors table has the rows

    1-56-604288-7, HARR, 1
    1-56-604288-7, KIDD, 2
    

    to indicate this fact. The third column lists the order of the authors. (We can't just use the position of the records in the table. There is no fixed row ordering in a relational table.) Thus, the query has to snake (join) itself from the Books table to the BooksAuthors table, then to the Authors table to compare the author name with the one selected by the user.

    SELECT Books.Price, Books.Title
    FROM Books, Publishers, BooksAuthors, Authors
    WHERE Books.Publisher_Id = Publishers.Publisher_Id
    AND Publishers.Name = ?
    AND Books.ISBN = BooksAuthors.ISBN
    AND BooksAuthors.Author = Authors.Author
    AND Authors.Name = ?
    

    TIP

    graphics/tip.gif
     

    Some Java programmers avoid complex SQL statements such as this one. A surprisingly common, but very inefficient, workaround is to write lots of Java code that iterates through multiple result sets. But the database is a lot better at executing query code than a Java program—that's the core competency of a database. As a rule of thumb: If you can do it in SQL, don't do it in Java.

  4. The results of the query are displayed in the results text box.

  5. When the user selects "Change prices," then the update query is constructed and executed. The query is quite complex because the WHERE clause of the UPDATE statement needs the publisher code and we know only the publisher name. This problem is solved with a nested subquery.

    UPDATE Books
    SET Price = Price + price change
    WHERE Books.Publisher_Id =
       (SELECT Publisher_Id
       FROM Publishers
       WHERE Name = publisher name)
    
  6. We initialize the connection and statement objects in the constructor. We hang on to them for the life of the program. Just before the program exits, we trap the "window closing" event, and these objects are closed.

class QueryDB extends Frame
{
   QueryDB()
   {
      conn = getConnection();
      stat = conn.createStatement();
      . . .
      add(new
         WindowAdapter()
         {
            public void windowClosing(WindowEvent event)
            {
               try
               {
                  stat.close();
                  conn.close();
               }
               catch(SQLException ex)
               {
                  while (ex != null)
                  {
                     ex.printStackTrace();
                     ex = ex.getNextException();
                  }
               }
            }
         });
   }
   . . .
   Connection conn;
   Statement stat;
}

Example 4-3 is the complete program code.

Example 4-3 QueryDB.java
  1. import java.net.*;
  2. import java.sql.*;
  3. import java.awt.*;
  4. import java.awt.event.*;
  5. import java.io.*;
  6. import java.util.*;
  7. import javax.swing.*;
  8.
  9. /**
 10.    This program demonstrates several complex database queries.
 11. */
 12. public class QueryDB
 13. {
 14.    public static void main(String[] args)
 15.    {
 16.       JFrame frame = new QueryDBFrame();
 17.       frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
 18.       frame.show();
 19.    }
 20. }
 21.
 22. /**
 23.    This frame displays combo boxes for query parameters,
 24.    a text area for command results, and buttons to launch
 25.    a query and an update.
 26. */
 27. class QueryDBFrame extends JFrame
 28. {
 29.    public QueryDBFrame()
 30.    {
 31.       setTitle("QueryDB");
 32.       setSize(WIDTH, HEIGHT);
 33.       getContentPane().setLayout(new GridBagLayout());
 34.       GridBagConstraints gbc = new GridBagConstraints();
 35.
 36.       authors = new JComboBox();
 37.       authors.setEditable(false);
 38.       authors.addItem("Any");
 39.
 40.       publishers = new JComboBox();
 41.       publishers.setEditable(false);
 42.       publishers.addItem("Any");
 43.
 44.       result = new JTextArea(4, 50);
 45.       result.setEditable(false);
 46.
 47.       priceChange = new JTextField(8);
 48.       priceChange.setText("-5.00");
 49.
 50.       try
 51.       {
 52.          conn = getConnection();
 53.          stat = conn.createStatement();
 54.
 55.          String query = "SELECT Name FROM Authors";
 56.          ResultSet rs = stat.executeQuery(query);
 57.          while (rs.next())
 58.             authors.addItem(rs.getString(1));
 59.          rs.close();
 60.
 61.          query = "SELECT Name FROM Publishers";
 62.          rs = stat.executeQuery(query);
 63.          while (rs.next())
 64.             publishers.addItem(rs.getString(1));
 65.          rs.close();
 66.       }
 67.       catch(SQLException ex)
 68.       {
 69.          result.setText("");
 70.          while (ex != null)
 71.          {
 72.             result.append("" + ex);
 73.             ex = ex.getNextException();
 74.          }
 75.       }
 76.       catch (IOException ex)
 77.       {
 78.          result.setText("" + ex);
 79.       }
 80.
 81.       gbc.fill = GridBagConstraints.NONE;
 82.       gbc.weightx = 100;
 83.       gbc.weighty = 100;
 84.       add(authors, gbc, 0, 0, 2, 1);
 85.
 86.       add(publishers, gbc, 2, 0, 2, 1);
 87.
 88.       gbc.fill = GridBagConstraints.NONE;
 89.       JButton queryButton = new JButton("Query");
 90.       queryButton.addActionListener(new
 91.          ActionListener()
 92.          {
 93.             public void actionPerformed(ActionEvent event)
 94.             {
 95.                executeQuery();
 96.             }
 97.          });
 98.       add(queryButton, gbc, 0, 1, 1, 1);
 99.
100.       JButton changeButton = new JButton("Change prices");
101.       changeButton.addActionListener(new
102.          ActionListener()
103.          {
104.             public void actionPerformed(ActionEvent event)
105.             {
106.                changePrices();
107.             }
108.          });
109.       add(changeButton, gbc, 2, 1, 1, 1);
110.
111.       gbc.fill = GridBagConstraints.HORIZONTAL;
112.       add(priceChange, gbc, 3, 1, 1, 1);
113.
114.       gbc.fill = GridBagConstraints.BOTH;
115.       add(new JScrollPane(result), gbc, 0, 2, 4, 1);
116.
117.       add(new
118.          WindowAdapter()
119.          {
120.             public void windowClosing(WindowEvent event)
121.             {
122.                try
123.                {
124.                   stat.close();
125.                   conn.close();
126.                }
127.                catch(SQLException ex)
128.                {
129.                   while (ex != null)
130.                   {
131.                      ex.printStackTrace();
132.                      ex = ex.getNextException();
133.                   }
134.                }
135.             }
136.          });
137.    }
138.
139.    /**
140.       Add a component to this frame.
141.       @param c the component to add
142.       @param gbc the grid bag constraints
143.       @param x the grid bax column
144.       @param y the grid bag row
145.       @param w the number of grid bag columns spanned
146.       @param h the number of grid bag rows spanned
147.    */
148.    private void add(Component c, GridBagConstraints gbc,
149.       int x, int y, int w, int h)
150.    {
151.       gbc.gridx = x;
152.       gbc.gridy = y;
153.       gbc.gridwidth = w;
154.       gbc.gridheight = h;
155.       getContentPane().add(c, gbc);
156.    }
157.
158.    /**
159.       Executes the selected query.
160.    */
161.    private void executeQuery()
162.    {
163.       ResultSet rs = null;
164.       try
165.       {
166.          String author
167.             = (String)authors.getSelectedItem();
168.          String publisher
169.             = (String)publishers.getSelectedItem();
170.          if (!author.equals("Any")
171.             && !publisher.equals("Any"))
172.          {
173.             if (authorPublisherQueryStmt == null)
174.             {
175.                String authorPublisherQuery =
176. "SELECT Books.Price, Books.Title " +
177. "FROM Books, BooksAuthors, Authors, Publishers " +
178. "WHERE Authors.Author_Id = BooksAuthors.Author_Id AND " +
179. "BooksAuthors.ISBN = Books.ISBN AND " +
180. "Books.Publisher_Id = Publishers.Publisher_Id AND " +
181. "Authors.Name = ? AND " +
182. "Publishers.Name = ?";
183.                authorPublisherQueryStmt
184.                   = conn.prepareStatement(authorPublisherQuery);
185.             }
186.             authorPublisherQueryStmt.setString(1, author);
187.             authorPublisherQueryStmt.setString(2,
188.                publisher);
189.             rs = authorPublisherQueryStmt.executeQuery();
190.          }
191.          else if (!author.equals("Any")
192.             && publisher.equals("Any"))
193.          {
194.             if (authorQueryStmt == null)
195.             {
196.                String authorQuery =
197. "SELECT Books.Price, Books.Title " +
198. "FROM Books, BooksAuthors, Authors " +
199. "WHERE Authors.Author_Id = BooksAuthors.Author_Id AND " +
200. "BooksAuthors.ISBN = Books.ISBN AND " +
201. "Authors.Name = ?";
202.                authorQueryStmt
203.                   = conn.prepareStatement(authorQuery);
204.             }
205.             authorQueryStmt.setString(1, author);
206.             rs = authorQueryStmt.executeQuery();
207.          }
208.          else if (author.equals("Any")
209.             && !publisher.equals("Any"))
210.          {
211.             if (publisherQueryStmt == null)
212.             {
213.                String publisherQuery =
214. "SELECT Books.Price, Books.Title " +
215. "FROM Books, Publishers " +
216. "WHERE Books.Publisher_Id = Publishers.Publisher_Id AND " +
217. "Publishers.Name = ?";
218.                publisherQueryStmt
219.                   = conn.prepareStatement(publisherQuery);
220.             }
221.             publisherQueryStmt.setString(1, publisher);
222.             rs = publisherQueryStmt.executeQuery();
223.          }
224.          else
225.          {
226.             if (allQueryStmt == null)
227.             {
228.                String allQuery =
229. "SELECT Books.Price, Books.Title FROM Books";
230.                allQueryStmt
231.                   = conn.prepareStatement(allQuery);
232.             }
233.             rs = allQueryStmt.executeQuery();
234.         }
235.
236.         result.setText("");
237.         while (rs.next())
238.         {
239.            result.append(rs.getString(1));
240.            result.append(", ");
241.            result.append(rs.getString(2));
242.            result.append("\n");
243.         }
244.         rs.close();
245.      }
246.      catch(SQLException ex)
247.      {
248.         result.setText("");
249.         while (ex != null)
250.         {
251.            result.append("" + ex);
252.            ex = ex.getNextException();
253.         }
254.      }
255.    }
256.
257.    /**
258.       Executes an update statement to change prices.
259.    */
260.    public void changePrices()
261.    {
262.       String publisher
263.          = (String)publishers.getSelectedItem();
264.       if (publisher.equals("Any"))
265.       {
266.          result.setText
267.             ("I am sorry, but I cannot do that.");
268.          return;
269.       }
270.       try
271.       {
272.          String updateStatement =
273. "UPDATE Books " +
274. "SET Price = Price + " + priceChange.getText() +
275. " WHERE Books.Publisher_Id = " +
276. "(SELECT Publisher_Id FROM Publishers WHERE Name = '" +
277.             publisher + "')";
278.          int r = stat.executeUpdate(updateStatement);
279.          result.setText(r + " records updated.");
280.       }
281.       catch(SQLException ex)
282.       {
283.          result.setText("");
284.          while (ex != null)
285.          {
286.             result.append("" + ex);
287.             ex = ex.getNextException();
288.          }
289.       }
290.    }
291.
292.    /**
293.       Gets a connection from the properties specified
294.       in the file database.properties
295.       @return the database connection
296.    */
297.    public static Connection getConnection()
298.       throws SQLException, IOException
299.    {
300.       Properties props = new Properties();
301.       FileInputStream in
302.          = new FileInputStream("database.properties");
303.       props.load(in);
304.       in.close();
305.
306.       String drivers = props.getProperty("jdbc.drivers");
307.       if (drivers != null)
308.          System.setProperty("jdbc.drivers", drivers);
309.       String url = props.getProperty("jdbc.url");
310.       String username = props.getProperty("jdbc.username");
311.       String password = props.getProperty("jdbc.password");
312.
313.       return
314.          DriverManager.getConnection(url, username, password);
315.    }
316.
317.    public static final int WIDTH = 400;
318.    public static final int HEIGHT = 400;
319.
320.    private JComboBox authors;
321.    private JComboBox publishers;
322.    private JTextField priceChange;
323.    private JTextArea result;
324.    private Connection conn;
325.    private Statement stat;
326.    private PreparedStatement authorQueryStmt;
327.    private PreparedStatement authorPublisherQueryStmt;
328.    private PreparedStatement publisherQueryStmt;
329.    private PreparedStatement allQueryStmt;
330. }

java.sql.Connection

graphics/api.gif
 

java.sql.PreparedStatement

graphics/api.gif
 

Scrollable and Updatable Result Sets

The most useful improvements in JDBC 2 are in the ResultSet class. As you have seen, the next method of the ResultSet class iterates over the rows in a result set. That is certainly adequate for a program that needs to analyze the data. However, consider a visual data display that shows a table or query result (see Figure 4-7). You usually want the user to be able to move both forward and backward in the result set. But in JDBC 1, there was no previous method. Programmers who wanted to implement backwards iteration had to manually cache the result set data. The scrolling result set in JDBC 2 lets you move forward and backward through a result set and jump to any position in the result set.

Figure 4-7. A GUI view of a query result

graphics/04fig07.gif

Furthermore, once you display the contents of a result set to users, they may be tempted to edit it. If you supply an editable view to your users, you have to make sure that the user edits are posted back to the database. In JDBC 1, you had to program UPDATE statements. In JDBC 2, you can simply update the result set entries, and the database is automatically updated.

JDBC 2 delivers additional enhancements to result sets, such as the capability of updating a result set with the most recent data if the data has been modified by another concurrent database connection. JDBC 3 adds another refinement, specifying the behavior of result sets when a transaction is committed. One form of the getStatement method of the Connection class lets you specify the holdability of the result sets. You can choose to have result sets automatically close when a transaction is committed, or to keep them open. However, these advanced features are outside the scope of this introductory chapter. We refer you to the JDBC API Tutorial and Reference by Seth White, et al. [Addison-Wesley 1999], and the JDBC specification documents at http://java.sun.com/products/jdbc for more information.

Scrollable Result Sets (JDBC 2)

To obtain scrolling result sets from your queries, you must obtain a different Statement object with the method

Statement stat = conn.createStatement(type, concurrency);

For a prepared statement, use the call

PreparedStatement stat = conn.prepareStatement(command,
   type, concurrency);

The possible values of type and concurrency are listed in Table 4-8 and Table 4-9. You have the following choices:

For example, if you simply want to be able to scroll through a result set but you don't want to edit its data, you use:

Statement stat
   = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_READ_ONLY);
Table 4-8. ResultSet type values
TYPE_FORWARD_ONLY The result set is not scrollable.
TYPE_SCROLL_INSENSITIVE The result set is scrollable but not sensitive to database changes.
TYPE_SCROLL_SENSITIVE The result set is scrollable and sensitive to database changes.

 

Table 4-9. ResultSet concurrency values
CONCUR_READ_ONLY The result set cannot be used to update the database.
CONCUR_UPDATABLE The result set can be used to update the database.

All result sets that are returned by method calls

ResultSet rs = stat.executeQuery(query)

are now scrollable. A scrolling result set has a cursor that indicates the current position.

NOTE

graphics/note.gif
 

Actually, a database driver might not be able to honor your request for a scrolling or updatable cursor. (The supportsResultSetType and supportsResultSetConcurrency methods of the DatabaseMetaData class tell you which types and concurrency modes are supported by a particular database.) But even if a database supports all result set modes, a particular query might not be able to yield a result set with all the properties that you requested. (For example, the result set of a complex query may not be updatable.) In that case, the executeQuery method returns a ResultSet of lesser capabilities and adds a warning to the connection object. You can retrieve warnings with the getWarnings method of the Connection class. Alternatively, you can use the getType and getConcurrency methods of the ResultSet class to find out what mode a result set actually has. If you do not check the result set capabilities and issue an unsupported operation, such as previous on a result set that is not scrollable, then the operation throws a SQLException.

CAUTION

graphics/caution.gif
 

In JDBC 1 drivers, the Connection class does not have a method

Statement createStatement(int type, int concurrency);

If a program that you compiled for JDBC 2 inadvertently loads a JDBC 1 driver and then calls this nonexistent method, the program will crash. Unfortunately, there is no JDBC 2 mechanism for querying a driver as to whether it is JDBC 2 compliant. In JDBC 3, you can use the getJDBCMajorVersion and getJDBCMinorVersion methods of the DatabaseMetaData class to find the JDBC version number of the driver.

Scrolling is very simple. You use

if (rs.previous()) . . .

to scroll backward. The method returns true if the cursor is positioned on an actual row; false if it now is positioned before the first row.

You can move the cursor backward or forward by a number of rows with the command

rs.relative(n);

If n is positive, the cursor moves forward. If n is negative, it moves backwards. If n is zero, the call has no effect. If you attempt to move the cursor outside the current set of rows, it is set to point either after the last row or before the first row, depending on the sign of n. Then, the method returns false and the cursor does not move. The method returns true if the cursor landed on an actual row.

Alternatively, you can set the cursor to a particular row number:

rs.absolute(n);

You get the current row number with the call

int n = rs.getRow();

The first row in the result set has number 1. If the return value is 0, the cursor is not currently on a row—it is either before the first or after the last row.

NOTE

graphics/note.gif
 

The order of the rows in a result set is completely arbitrary. You should never attach any significance to the row numbers.

There are convenience methods

first
last
beforeFirst
afterLast

to move the cursor to the first, to the last, before the first, or after the last position.

Finally, the methods

isFirst
isLast
isBeforeFirst
isAfterLast

test whether the cursor is at one of these special positions.

Using a scrollable result set is very simple. The hard work of caching the query data is carried out behind the scenes by the database driver.

TIP

graphics/tip.gif
 

If you want to implement a scrollable view in a web application, then the scrollable result set has a major drawback. It requires you to keep the connection to the database open between page requests. However, users can walk away from their computer for a long time, or simply abandon your site, leaving the connection occupied. That is not good—database connections are scarce resources. In such a situation, use a row set. The RowSet interface extends the ResultSet interface, but row sets aren't tied to a database connection. They can be cached, or they can present data from a source other than a database. The article http://developer.java.sun.com/developer/technicalArticles/javaserverpages/cachedrowset shows how to use row sets in a web application.

Updatable Result Sets (JDBC 2)

If you want to be able to edit result set data and have the changes automatically reflected in the database, you need to create an updatable result set. Updatable result sets don't have to be scrollable, but if you present data to a user for editing, you usually want to allow scrolling as well.

To obtain updatable result sets, you create a statement as follows.

Statement stat
   = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_UPDATABLE);

Then, the result sets returned by a call to executeQuery are updatable.

NOTE

graphics/note.gif
 

Not all queries return updatable result sets. If your query is a join that involves multiple tables, the result may not be updatable. If your query involves only a single table or if it joins multiple tables by their primary keys, you should expect the result set to be updatable. Call the getConcurrency method of the ResultSet class to find out for sure.

For example, suppose you want to raise the prices of some books, but you don't have a simple criterion for issuing an UPDATE command. Then, you can iterate through all books and update prices, based on arbitrary conditions.

String query = "SELECT * FROM Books";
ResultSet rs = stat.executeQuery(query);
while (rs.next())
{
   if (. . .)
   {
      double increase = . . .
      double price = rs.getDouble("Price");
      rs.updateDouble("Price", price + increase);
      rs.updateRow();
   }
}

There are updateXxx methods for all data types that correspond to SQL types, such as updateDouble, updateString, and so on. As with the getXxx methods, you specify the name or the number of the column. Then, you specify the new value for the field.

NOTE

graphics/note.gif
 

If you use the updateXxx method whose first parameter is the column number, be aware that this is the column number in the result set. It may well be different from the column number in the database.

The updateXxx method only changes the row values, not the database. When you are done with the field updates in a row, you must call the updateRow method. That method sends all updates in the current row to the database. If you move the cursor to another row without calling updateRow, all updates are discarded from the row set and they are never communicated to the database. You can also call the cancelRowUpdates method to cancel the updates to the current row.

The preceding example shows how you modify an existing row. If you want to add a new row to the database, you first use the moveToInsertRow method to move the cursor to a special position, called the insert row. You build up a new row in the insert row position by issuing updateXxx instructions. Finally, when you are done, call the insertRow method to deliver the new row to the database. When you are done inserting, call moveToCurrentRow to move the cursor back to the position before the call to moveToInsertRow. Here is an example.

rs.moveToInsertRow();
rs.updateString("Title", title);
rs.updateString("ISBN", isbn);
rs.updateString("Publisher_Id", pubid);
rs.updateString("URL", url);
rs.updateDouble("Price", price);
rs.insertRow();
rs.moveToCurrentRow();

Note that you have no influence where the new data is added in the result set or the database.

Finally, you can delete the row under the cursor.

rs.deleteRow();

The deleteRow method immediately removes the row from both the result set and the database.

The updateRow, insertRow, and deleteRow methods of the ResultSet class give you the same power as executing UPDATE, INSERT, and DELETE SQL commands. However, programmers who are used to the Java programming language will find it more natural to manipulate the database contents through result sets than by constructing SQL statements. Once JDBC 2 drivers are widely available, this programming style is likely to become quite popular.

CAUTION

graphics/caution.gif
 

If you are not careful, you can write staggeringly inefficient code with updatable result sets. It is much more efficient to execute an UPDATE statement than it is to make a query and iterate through the result, changing data along the way. Updatable result sets make sense for interactive programs in which a user can make arbitrary changes, but for most programmatic changes, a SQL UPDATE is more appropriate.

javax.sql.Connection

graphics/api.gif
 

java.sql.ResultSet

graphics/api.gif
 

java.sql.DatabaseMetaData

graphics/api.gif
 

Metadata

In the preceding sections, you saw how to populate, query, and update database tables. However, JDBC can give you additional information about the structure of a database and its tables. For example, you can get a list of the tables in a particular database or the column names and types of a table. This information is not useful when you are implementing a particular database application. After all, if you design the tables, you know the tables and their structure. Structural information is, however, extremely useful for programmers who write tools that work with any database.

In this section, we will show you how to write such a simple tool. This tool lets you browse all tables in a database.

The combo box on top displays all tables in the database. Select one of them, and the center of the frame is filled with the field names of that table and the values of the first record, as shown in Figure 4-8. Click on "Next" to scroll through the records in the table.

Figure 4-8. The ViewDB application

graphics/04fig08.gif

We fully expect tool vendors to develop much more sophisticated versions of programs like this one. For example, it would be possible to let the user edit the values or add new ones, and then update the database. We developed this program mostly to show you how such tools can be built.

In SQL, data that describes the database or one of its parts is called metadata (to distinguish it from the actual data that is stored in the database). You can get two kinds of metadata: about a database and about a result set.

To find out more about the database, you need to request an object of type DatabaseMetaData from the database connection.

DatabaseMetaData meta = conn.getMetaData();

Now you are ready to get some metadata. For example, the call

ResultSet rs = meta.getTables(null, null, null, new String[]
   { "TABLE" });

returns a result set that contains information about all tables in the database. (See the API note for other parameters to this method.)

Each row in the result set contains information about the table. We only care about the third entry, the name of the table. (Again, see the API note for the other columns.) Thus, rs.getString(3) is the table name. Here is the code that populates the combo box.

while (rs.next())
   tableNames.addItem(rs.getString(3));
rs.close();

The DatabaseMetaData class gives data about the database. There is a second metadata class, ResultSetMetaData, that reports information about a result set. Whenever you have a result set from a query, you can inquire about the number of columns and each column's name, type, and field width.

We will make use of this information to make a label for each name and a text field of sufficient size for each value.

ResultSet rs = stat.executeQuery("SELECT * FROM " + tableName);
ResultSetMetaData meta = rs.getMetaData();
for (int i = 1; i <= meta.getColumnCount(); i++)
{
   String columnName = meta.getColumnLabel(i);
   int columnWidth = meta.getColumnDisplaySize(i);
   Label l = new Label(columnName);
   TextField tf = new TextField(columnWidth);
   . . .
}

There is a second important use for database meta data. Databases are complex, and the SQL standard leaves plenty of room for variability. There are well over a hundred methods in the DatabaseMetaData class to inquire about the database, including calls with exotic names such as

md.supportsCatalogsInPrivilegeDefinitions()

and

md.nullPlusNonNullIsNull()

Clearly, these are geared toward advanced users with special needs, in particular, those who need to write highly portable code that works with multiple databases. In our sample program, we only give one example of this technique. We ask the database metadata whether the JDBC driver supports scrolling result sets. If so, we open a scrolling result set and add a "Previous" button for scrolling backwards.

if (meta.supportsResultSetType(
   ResultSet.TYPE_SCROLL_INSENSITIVE)) . . .

The following steps provide a brief overview of the sample program.

  1. Have the border layout put the table name combo box on the top and the table values in the center.

  2. Connect to the database. Find out if it supports scrolling result sets. If so, create the Statement object to yield scrolling result sets. Otherwise, just create a default Statement.

  3. Get the table names and fill them into the choice component.

  4. If scrolling is supported, add the "Previous" button. Always add the "Next" button.

  5. When the user selects a table, make a query to see all its values. Get the result set metadata. Throw out the old scroll pane from the center panel. Create a panel containing a gridbag layout of labels and text fields. Add it to the frame and call the validate method to recompute the frame layout. Then, call showNextRow to show the first row.

  6. The showNextRow method is called to show the first record and is also called whenever the "Next" button is clicked. It gets the next row from the table and fills the column values into the text boxes.

  7. There is a slight subtlety in detecting the end of the result set. When the result set is scrolling, we can simply use the isLast method. But when it isn't scrolling, that method call will cause an exception (or even a JVM error if the driver is a JDBC 1 driver). Therefore, we use a different strategy for non-scrolling result sets. When rs.next() returns false, we close the result set and set rs to null.

  8. The "Previous" button calls showPreviousRow, which moves the result set backwards. Since this button is only installed when the result set is scrollable, we know that the previous and isFirst method are supported.

  9. The showRow method simply fills in all the result set fields into the text fields of the data panel.

Example 4-4 is the program.

Example 4-4 ViewDB.java
  1. import java.net.*;
  2. import java.sql.*;
  3. import java.awt.*;
  4. import java.awt.event.*;
  5. import java.io.*;
  6. import java.util.*;
  7. import javax.swing.*;
  8.
  9. /**
 10.    This program uses metadata to display arbitrary tables
 11.    in a database.
 12. */
 13. public class ViewDB
 14. {
 15.    public static void main(String[] args)
 16     {
 17.       JFrame frame = new ViewDBFrame();
 18.       frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
 19.       frame.show();
 20.    }
 21. }
 22.
 23. /**
 24.    The frame that holds the data panel and the navigation
 25.    buttons.
 26. */
 27. class ViewDBFrame extends JFrame
 28. {
 29.    public ViewDBFrame()
 30.    {
 31.       setTitle("ViewDB");
 32.       setSize(WIDTH, HEIGHT);
 33.
 34.       Container contentPane = getContentPane();
 35.
 36.       tableNames = new JComboBox();
 37.       tableNames.addActionListener(new
 38.          ActionListener()
 39.          {
 40.             public void actionPerformed(ActionEvent event)
 41.             {
 42.                showTable((String)tableNames.getSelectedItem());
 43.             }
 44.          });
 45.       contentPane.add(tableNames, BorderLayout.NORTH);
 46.
 47.       try
 48.       {
 49.          conn = getConnection();
 50.          meta = conn.getMetaData();
 51.          createStatement();
 52.          getTableNames();
 53.       }
 54.       catch(Exception ex)
 55.       {
 56.          JOptionPane.showMessageDialog(this, ex);
 57.       }
 58.
 59.       JPanel buttonPanel = new JPanel();
 60.       contentPane.add(buttonPanel, BorderLayout.SOUTH);
 61.
 62.       if (scrolling)
 63.       {
 64.          previousButton = new JButton("Previous");
 65.          previousButton.addActionListener(new
 66.             ActionListener()
 67.             {
 68.                public void actionPerformed(ActionEvent event)
 69.                {
 70.                   showPreviousRow();
 71.                }
 72.             });
 73.          buttonPanel.add(previousButton);
 74.       }
 75.
 76.       nextButton = new JButton("Next");
 77.       nextButton.addActionListener(new
 78.          ActionListener()
 79.          {
 80.             public void actionPerformed(ActionEvent event)
 81.             {
 82.                showNextRow();
 83.             }
 84.          });
 85.       buttonPanel.add(nextButton);
 86.
 87.       add(new
 88.          WindowAdapter()
 89.          {
 90.             public void windowClosing(WindowEvent event)
 91.             {
 92.                try
 93.                {
 94.                   stat.close();
 95.                   conn.close();
 96.                }
 97.                catch(SQLException ex)
 98.                {
 99.                   while (ex != null)
100.                   {
101.                      ex.printStackTrace();
102.                      ex = ex.getNextException();
103.                   }
104.                }
105.             }
106.          });
107.     }
108.  }
109.
110.    /**
111.       Creates the statement object used for executing queries.
112.       If the database supports scrolling cursors, the statement
113.       is created to yield them.
114.    */
115.    public void createStatement() throws SQLException
116.    {
117.       if (meta.supportsResultSetType(
118.          ResultSet.TYPE_SCROLL_INSENSITIVE))
119.       {
120.          stat = conn.createStatement(
121.             ResultSet.TYPE_SCROLL_INSENSITIVE,
122.             ResultSet.CONCUR_READ_ONLY);
123.          scrolling = true;
124.       }
125.       else
126.       {
127.          stat = conn.createStatement();
128.          scrolling = false;
129.       }
130.    }
131.
132.    /**
133.       Gets all table names of this database and adds them
134.       to the combo box.
135.    */
136.    public void getTableNames() throws SQLException
137.    {
138.       ResultSet mrs = meta.getTables(null, null, null,
139.          new String[] { "TABLE" });
140.       while (mrs.next())
141.          tableNames.addItem(mrs.getString(3));
142.       mrs.close();
143.    }
144.
145.    /**
146.       Prepares the text fields for showing a new table, and
147.       shows the first row.
148.       @param tableName the name of the table to display
149.    */
150.    public void showTable(String tableName)
151.    {
152.       try
153.       {
154.          if (rs != null) rs.close();
155.          rs = stat.executeQuery("SELECT * FROM " + tableName);
156.          if (scrollPane != null)
157.             getContentPane().remove(scrollPane);
158.          dataPanel = new DataPanel(rs);
159.          scrollPane = new JScrollPane(dataPanel);
160.          getContentPane().add(scrollPane, BorderLayout.CENTER);
161.          validate();
162.          showNextRow();
163.       }
164.       catch(Exception ex)
165.       {
166.          JOptionPane.showMessageDialog(this, ex);
167.       }
168.    }
169.
170.    /**
171.       Moves to the previous table row.
172.    */
173.    public void showPreviousRow()
174.    {
175.       try
176.       {
177.          if (rs == null || rs.isFirst()) return;
178.          rs.previous();
179.          dataPanel.showRow(rs);
180.       }
181.       catch(Exception ex)
182.       {
183.          System.out.println("Error " + ex);
184.       }
185.    }
186.
187.    /**
188.       Moves to the next table row.
189.    */
190.    public void showNextRow()
191.    {
192.       try
193.       {
194.          if (rs == null || scrolling && rs.isLast()) return;
195.
196.          if (!rs.next() && !scrolling)
197.          {
198.             rs.close();
199.             rs = null;
200.             return;
201.          }
202.
203.          dataPanel.showRow(rs);
204.       }
205.       catch(Exception ex)
206.       {
207.          System.out.println("Error " + ex);
208.       }
209.    }
210.
211.    /**
212.       Gets a connection from the properties specified
213.       in the file database.properties
214.       @return the database connection
215.    */
216.    public static Connection getConnection()
217.       throws SQLException, IOException
218.    {
219.       Properties props = new Properties();
220.       FileInputStream in
221.          = new FileInputStream("database.properties");
222.       props.load(in);
223.       in.close();
224.
225.       String drivers = props.getProperty("jdbc.drivers");
226.       if (drivers != null)
227.          System.setProperty("jdbc.drivers", drivers);
228.       String url = props.getProperty("jdbc.url");
229.       String username = props.getProperty("jdbc.username");
230.       String password = props.getProperty("jdbc.password");
231.
232.       return
233.          DriverManager.getConnection(url, username, password);
234.    }
235.
236.    public static final int WIDTH = 300;
237.    public static final int HEIGHT = 200;
238.
239.    private JButton previousButton;
240.    private JButton nextButton;
241.    private DataPanel dataPanel;
242.    private JScrollPane scrollPane;
243.    private JComboBox tableNames;
244.
245.    private Connection conn;
246.    private Statement stat;
247.    private DatabaseMetaData meta;
248.    private ResultSet rs;
249.    private boolean scrolling;
250. }
251.
252. /**
253.    This panel displays the contents of a result set.
254. */
255. class DataPanel extends JPanel
256. {
257.    /**
258.       Constructs the data panel.
259.       @param rs the result set whose contents this panel displays
260.    */
261.    public DataPanel(ResultSet rs) throws SQLException
262.    {
263.       fields = new ArrayList();
264.       setLayout(new GridBagLayout());
265.       GridBagConstraints gbc = new GridBagConstraints();
266.       gbc.weighty = 100;
267.
268.       ResultSetMetaData rsmd = rs.getMetaData();
269.       for (int i = 1; i <= rsmd.getColumnCount(); i++)
270.       {
271.          String columnName = rsmd.getColumnLabel(i);
272.          int columnWidth = rsmd.getColumnDisplaySize(i);
273.          JTextField tb = new JTextField(columnWidth);
274.          fields.add(tb);
275.
276.          gbc.weightx = 0;
277.          gbc.anchor = GridBagConstraints.EAST;
278.          gbc.fill = GridBagConstraints.NONE;
279.          add(new JLabel(columnName), gbc, 0, i - 1, 1, 1);
280.
281.          gbc.weightx = 100;
282.          gbc.anchor = GridBagConstraints.WEST;
283.          gbc.fill = GridBagConstraints.HORIZONTAL;
284.          add(tb, gbc, 1, i - 1, 1, 1);
285.       }
286.    }
287.
288.    /**
289.       Shows a database row by populating all text fields
290.       with the column values.
291.    */
292.    public void showRow(ResultSet rs) throws SQLException
293.    {
294.       for (int i = 1; i <= fields.size(); i++)
295.       {
296.          String field = rs.getString(i);
297.          JTextField tb
298.             = (JTextField)fields.get(i - 1);
299.          tb.setText(field);
300.       }
301.    }
302.
303.    /**
304.       Adds a component to this panel.
305.       @param c the component to add
306.       @param gbc the grid bag constraints
307.       @param x the grid bax column
308.       @param y the grid bag row
309.       @param w the number of grid bag columns spanned
310.       @param h the number of grid bag rows spanned
311.    */
312.    private void add(Component c,
313.       GridBagConstraints gbc, int x, int y, int w, int h)
314.    {
315.       gbc.gridx = x;
316.       gbc.gridy = y;
317.       gbc.gridwidth = w;
318.       gbc.gridheight = h;
319.       add(c, gbc);
320.    }
321.
322.    private ArrayList fields;
323.}

java.sql.Connection

graphics/api.gif
 

java.sql.DatabaseMetaData

graphics/api.gif
 

java.sql.ResultSet

graphics/api.gif
 

java.sql.ResultSetMetaData

graphics/api.gif
 

Transactions

While we do not want to go deep into transaction support, we do want to show you how to group a set of statements to form a transaction that can be committed when all has gone well, or rolled back as if none of the commands had been issued if an error has occurred in one of them.

The major reason for grouping commands into transactions is database integrity. For example, suppose we want to add a new book to our book database. Then, it is important that we simultaneously update the Books, Authors, and BooksAuthors table. If the update were to add new rows into the first two tables but not into the third, then the books and authors would not be properly matched up.

If you group updates to a transaction, then the transaction either succeeds in its entirety and it can be committed, or it fails somewhere in the middle. In that case, you can carry out a rollback and the database automatically undoes the effect of all updates that occurred since the last committed transaction. Furthermore, queries are guaranteed to report only on the committed state of the database.

By default, a database connection is in autocommit mode, and each SQL command is committed to the database as soon as it is executed. Once a command is committed, you cannot roll it back.

To check the current autocommit mode setting, call the getAutoCommit method of the Connection class.

You turn off autocommit mode with the command

conn.setAutoCommit(false);

Now you create a statement object in the normal way:

Statement stat = conn.createStatement();

Call executeUpdate any number of times:

stat.executeUpdate(command1);
stat.executeUpdate(command2);
stat.executeUpdate(command3);
. . .

When all commands have been executed, call the commit method:

conn.commit();

However, if an error occurred, call

conn.rollback();

Then, all commands until the last commit are automatically reversed. You typically issue a rollback when your transaction was interrupted by a SQLException.

Batch Updates (JDBC 2)

Suppose a program needs to execute many INSERT statements to populate a database table. In JDBC 2, you can improve the performance of the program by using a batch update. In a batch update, a sequence of commands is collected and submitted as a batch.

NOTE

graphics/note.gif
 

Use the supportsBatchUpdates method of the DatabaseMetaData class to find out if your database supports this feature.

The commands in a batch can be actions such as INSERT, UPDATE, and DELETE as well as data definition commands such as CREATE TABLE and DROP TABLE. However, you cannot add SELECT commands to a batch since executing a SELECT statement returns a result set.

To execute a batch, you first create a Statement object in the usual way:

Statement stat = conn.createStatement();

Now, instead of calling executeUpdate, you call the addBatch method:

String command = "CREATE TABLE . . ."
stat.addBatch(command);

while (. . .)
{
   command = "INSERT INTO . . . VALUES (" + . . . + ")";
   stat.addBatch(command);
}

Finally, you submit the entire batch.

int[] counts = stat.executeBatch();

The call to executeBatch returns an array of the row counts for all submitted commands. (Recall that an individual call to executeUpdate returns an integer, namely, the count of the rows that are affected by the command.) In our example, the executeBatch method returns an array with first element equal to 0 (because the CREATE TABLE command yields a row count of 0) and all other elements equal to 1 (because each INSERT command affects one row).

For proper error handling in batch mode, you want to treat the batch execution as a single transaction. If a batch fails in the middle, you want to roll back to the state before the beginning of the batch.

First, turn autocommit mode off, then collect the batch, execute it, commit it, and finally restore the original autocommit mode:

boolean autoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
Statement stat = conn.getStatement();
. . .
// keep calling stat.addBatch(. . .);
. . .
stat.executeBatch();
conn.commit();
conn.setAutoCommit(autoCommit);
java.sql.Connection

graphics/api.gif
 

java.sql.Statement

graphics/api.gif
 

java.sql.DatabaseMetaData

graphics/api.gif
 

Advanced Connection Management

The simplistic database connection setup of the preceding sections, with a database.properties file, is suitable for small test programs, but it won't scale for larger applications.

When deploying a JDBC 2 application in an enterprise environment, the management of database connections is integrated with the Java Naming and Directory Interface (JNDI). A directory manages the location of data sources across the enterprise. Using a directory allows for centralized management of user names, passwords, database names, and JDBC URLs.

In such an environment, you use the following code to establish a database connection:

Context jndiContext = . . .;
DataSource source
   = (DataSource)jndiContext.lookup("jdbc/corejava");
Connection conn = source.getConnection(username, password);

Note that the DriverManager is no longer involved. Instead, the JNDI service locates a data source. A data source is an interface that allows for simple JDBC connections as well as more advanced services, such as executing distributed transactions that involve multiple databases. The DataSource interface is defined in the javax.sql standard extension package.

Management of user names and logins is just one of the issues that require special attention. A second issue involves the cost of establishing database connections.

Our simple database programs established a single database connection at the start of the program and closed it at the end of the program. However, in many programming situations, this approach won't work. Consider a typical web application. Such an application serves multiple page requests in parallel. Multiple requests may need simultaneous access the database. With many databases, a connection is not intended to be shared by multiple threads. Thus, each request needs its own connection. A simplistic approach would be to establish a connection for each page request and close it afterwards. But that would be very costly. Establishing a database connection can be quite time-consuming. Connections are intended to be used for multiple queries, not to be closed after one or two queries.

The solution is to pool the connections. This means that database connections are not physically closed but are kept in a queue and reused. Connection pooling is an important service, and the JDBC specification provides hooks for implementors to supply it. However, the Java SDK itself does not provide any implementation, and database vendors don't usually include one with their JDBC driver either. Instead, vendors of application servers such as BEA WebLogic or IBM WebSphere supply connection pool implementations as part of the application server package.

Using a connection pool is completely transparent to the programmer. You acquire a connection from a source of pooled connections, by obtaining a data source and calling getConnection. When you are done using the connection, call close. That doesn't close the physical connection but tells the pool that you are done using it.

TIP

graphics/tip.gif
 

If you deploy a simple Web application, using just a servlet container such as Apache Tomcat, you need to supply your own connection pool. Implementations of varying quality are available on the Internet. If you only need support for a single database login, and you don't use transactions, then the implementation at http://developer.java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html should get you started.

CAUTION

graphics/caution.gif
 

In Web applications, you need to be particularly careful about recycling connections when an error occurs. Suppose an exception is thrown and the call to close is skipped. Then a user can drain the connection pool quickly simply by hitting the browser's "Reload" button in frustration, executing the same faulty service request over and over. Be sure to close the connections in finally clauses, like this:

public void myService() throws SQLException
{
   Connection conn = null;
   try
   {
      conn = ds.getConnection();
      . . .
   }
   finally
   {
      if (conn != null) conn.close();
   }
}

This code has one flaw—if the conn.close()call throws an exception, it masks the original exception. That's an unfortunate consequence of the Java exception handling mechanism. However, saving and rethrowing the original exception would be fiendishly complex—we leave it as the proverbial exercise for the reader.

You have now learned about the JDBC fundamentals and know enough to implement simple database applications. However, as we mentioned at the beginning of this chapter, databases are complex and there are quite a few advanced topics that are beyond the scope of an introductory chapter. For an overview of advanced JDBC capabilities, you can read the JDBC specifications at http://java.sun.com/products/jdbc.

CONTENTS